#!/usr/bin/env python
# coding=utf-8
import MySQLdb

def select_db(sql):
	ps_db=MySQLdb.connect(host='127.0.0.1', user='lrq', passwd='123456', db='hr_talent_management', use_unicode=True, charset="utf8")
	cur = ps_db.cursor()
	cur.execute(sql)
	dataset = cur.fetchall()
	cur.close()
	ps_db.close()
	return dataset


def unicode_utf8(field):
	if field and type(field) == unicode:
		return field.encode('utf-8')
	elif field:
		return field
	return ''


if __name__ == '__main__':
	ps_db=MySQLdb.connect(host='127.0.0.1', user='lrq', passwd='123456', db='db_tms', use_unicode=True, charset="utf8")

	# PositionFamily
	cur_pf = ps_db.cursor()
	print ' ------ position_family sync begin ------'
	for row in select_db("select a.id, a.family_code, a.family_name, a.update_date, b.username from organization_jobfamily a, auth_user b where a.update_by_id = b.id"):
		print row[0], row[1], row[2], row[3], row[4]
		cur_pf.execute("""insert into position_family(`id`, `key`, `val`, `ctime`, `cperson`, `utime`, `uperson`) VALUES (%s, %s, %s, %s, %s, %s ,%s)""", 
			[row[0], row[1], unicode_utf8(row[2]), unicode_utf8(row[3]), unicode_utf8(row[4]), unicode_utf8(row[3]), unicode_utf8(row[4]) ])
	cur_pf.close()
	print ' ------ position_family sync end ------'

	# PositionSubFamily
	cur_psf = ps_db.cursor()
	print ' ------ position_sub_family sync begin ------'
	for row in select_db("""select a.id, a.jobfamily_id, a.sub_family_code, a.sub_family_name, a.update_date, b.username from organization_jobsubfamily a, auth_user b where a.update_by_id = b.id"""):
		print row[0], row[1], row[2], row[3], row[4], row[5]
		cur_psf.execute("""insert into position_sub_family(`id`, `parent`, `key`, `val`, `ctime`, `cperson`, `utime`, `uperson`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)""", 
			[row[0], row[1], row[2], unicode_utf8(row[3]), unicode_utf8(row[4]), unicode_utf8(row[5]), unicode_utf8(row[4]), unicode_utf8(row[5]) ])
	cur_psf.close()
	print ' ------ position_sub_family sync end ------'

	# PositionMap
	cur_pm = ps_db.cursor()
	print ' ------ position_map sync begin ------'
	for row in select_db("""select a.id, a.job_sub_family_id, a.position_code, a.position_title, a.update_date, b.username from organization_position a, auth_user b where a.update_by_id = b.id"""):
		print row[0], row[1], row[2], row[3], row[4], row[5]
		cur_pm.execute("""insert into position_map(`id`, `parent`, `key`, `val`, `ctime`, `cperson`, `utime`, `uperson`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)""", 
			[row[0], row[1], unicode_utf8(row[2]), unicode_utf8(row[3]), unicode_utf8(row[4]), unicode_utf8(row[5]), unicode_utf8(row[4]), unicode_utf8(row[5])])
	cur_pm.close()
	print ' ------ position_map sync end ------'


	# Division
	cur_division = ps_db.cursor()
	print ' ------ division sync begin ------'
	for row in select_db("""select a.id, a.division_name, a.division_name_short, a.update_date, b.username from organization_businessdivision a, auth_user b where a.update_by_id = b.id"""):
		print row[0], row[1], row[2], row[3], row[4]
		cur_division.execute("""insert into division(`id`, `key`, `val`, `short`, `ctime`, `cperson`, `utime`, `uperson`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)""", 
			[row[0], '', unicode_utf8(row[1]), unicode_utf8(row[2]), unicode_utf8(row[3]), unicode_utf8(row[4]), unicode_utf8(row[3]), unicode_utf8(row[4])])
	cur_division.close()
	print ' ------ division sync end ------'

	# ProductionTeam
	cur_pt = ps_db.cursor()
	print ' ------ production_team sync begin ------'
	for row in select_db("""select a.id, a.division_id, a.team_name, a.team_name_short, a.update_date, b.username from organization_productionteam a, auth_user b where a.update_by_id = b.id"""):
		print row[0], row[1], row[2], row[3], row[4], row[5]
		cur_pt.execute("""insert into production_team(`id`, `parent`, `key`, `val`, `short`, `ctime`, `cperson`, `utime`, `uperson`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)""", 
			[row[0], row[1], '', unicode_utf8(row[2]), unicode_utf8(row[3]), unicode_utf8(row[4]), unicode_utf8(row[5]), unicode_utf8(row[4]), unicode_utf8(row[5])])
	cur_pt.close()
	print ' ------ production_team sync end ------'

	# StaffInfo
	cur_staffinfo = ps_db.cursor()
	print ' ------ staff_info sync begin ------'
	for row in select_db("""SELECT a.id, a.Chinese_Name, a.English_Name, a.Employee_ID, c.Qr_Code, b.Job_Nature, 
		b.Job_Level, b.Employment_Type, a.Work_Email, a.Employment_Status, a.On_Board_Date, a.Departure_Date,
		b.Position_id, a.update_date, d.username FROM staff_basicinfo a, staff_jobinfo b, 
		personal_personnelinfo c, auth_user d WHERE a.id = b.basic_info_id AND a.id = c.basic_info_id and b.End_Date is null and a.update_by_id=d.id """):
		cur_staffinfo.execute("""insert into staff_info(`id`, `cn_name`, `en_name`, `emp_id`, `qr_code`, `job_nature`, `job_level`,
		`emp_type`, `wemail`, `ioboard`, `obdate`, `dtdate`, `postion`, `ctime`, `cperson`, `utime`, `uperson`) 
		VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", 
		[row[0], unicode_utf8(row[1]), row[2], row[3], row[4], row[5], '' if row[6] is None else row[6], row[7], row[8], 
		1 if row[9] == 'W' else 0, row[10], '' if row[11] is None else row[11], row[12], unicode_utf8(row[13]), unicode_utf8(row[14]),
		unicode_utf8(row[13]), unicode_utf8(row[14])])
	cur_staffinfo.close()
	print ' ------ staff_info sync end ------'

	# PersonInfo
	cur_personinfo = ps_db.cursor()
	print ' ------ person_info sync begin ------'
	for row in select_db("""SELECT c.id, c.Basic_Info_id, c.gender, c.age, c.Date_of_birth, c.nation, 
		c.nationality, c.native_place, c.Health_status, c.Party_States, c.former_name, c.Profile_Photo, 
		'' life_photo, c.Place_of_SocialIns, c.Place_of_Housing_Fund, c.IDType, c.IDNumber,
		c.Address_Document, c.ID_EndTime, c.Place_Of_Residence, c.Household_Type, c.Mobile_No, c.Telephone, 
		c.email, c.QQ, c.weixin, c.Current_Living_Address, c.emergency_contact, c.Marital_Status, c.Fertility_Status, 
		c.child, c.Relationship, c.Hobbies, c.Heigh, c.weight, c.Blood_Type, c.illness, c.criminal, c.update_date FROM personal_personnelinfo c, staff_basicinfo a, staff_jobinfo b 
		where c.Basic_Info_id = a.id and a.id = b.basic_info_id and b.End_Date is null """):
		print row[1] 
		cur_personinfo.execute("""insert into person_info(`id`, `staffinfo_id`, `gender`, `age`, `born_date`, `nation`, 
			`nationality`, `native_place`, `health`, `party`, `former_name`, `formal_photo`, `life_photo`, `siplace`, 
			`hfplace`, `cert_type`, `cert_number`, `cert_address`, `cert_valid_date`, `domicile_place`, `domicile_type`, 
			`tel`, `htel`, `email`, `qq`, `weixin`, `live_address`, `emergency_contact`, `marriage`, `fertility`, `child`, 
			`family`, `hobby`, `height`, `weight`, `blood`, `illness`, `criminal`, `ctime`, `cperson`, `utime`, `uperson`) VALUES (%(id)s, %(staffinfo_id)s, %(gender)s, %(age)s, %(born_date)s, %(nation)s, 
			%(nationality)s, %(native_place)s, %(health)s, %(party)s, %(former_name)s, %(formal_photo)s, %(life_photo)s, %(siplace)s, %(hfplace)s, %(cert_type)s,
			%(cert_number)s, %(cert_address)s, %(cert_valid_date)s, %(domicile_place)s, %(domicile_type)s, %(tel)s, %(htel)s, %(email)s, %(qq)s, %(weixin)s, %(live_address)s, 
			%(emergency_contact)s, %(marriage)s, %(fertility)s, %(child)s, %(family)s, %(hobby)s, %(height)s, %(weight)s, %(blood)s, %(illness)s, %(criminal)s, 
			%(ctime)s, %(cperson)s, %(utime)s, %(uperson)s)""", { 
			'id': row[0], 'staffinfo_id': row[1], 'gender': row[2], 'age': row[3], 'born_date': row[4], 'nation': unicode_utf8(row[5]), 'nationality': unicode_utf8(row[6]), 
			'native_place': unicode_utf8(row[7]), 'health': unicode_utf8(row[8]), 'party': unicode_utf8(row[9]), 'former_name': unicode_utf8(row[10]), 'formal_photo': row[11], 
			'life_photo': row[12], 'siplace': unicode_utf8(row[13]), 'hfplace': unicode_utf8(row[14]), 'cert_type': unicode_utf8(row[15]),
			'cert_number': row[16], 'cert_address': unicode_utf8(row[17]), 'cert_valid_date': unicode_utf8(row[18]), 'domicile_place': unicode_utf8(row[19]), 
			'domicile_type': unicode_utf8(row[20]), 'tel': unicode_utf8(row[21]), 'htel': unicode_utf8(row[22]), 'email': unicode_utf8(row[23]), 
			'qq': unicode_utf8(row[24]), 'weixin': unicode_utf8(row[25]), 'live_address': unicode_utf8(row[26]), 'emergency_contact': unicode_utf8(row[27]), 
			'marriage': unicode_utf8(row[28]), 'fertility': unicode_utf8(row[29]), 'child': unicode_utf8(row[30]),
			'family': unicode_utf8(row[31]), 'hobby': unicode_utf8(row[32]), 'height': row[33], 'weight': row[34], 'blood': unicode_utf8(row[35]), 'illness': unicode_utf8(row[36]),
			'criminal': unicode_utf8(row[37]), 'ctime': unicode_utf8(row[38]), 'cperson': 'imported', 'utime': unicode_utf8(row[38]), 'uperson': 'imported' })
	cur_personinfo.close()
	print ' ------ person_info sync end ------'

	# EducationExp
	cur_edu = ps_db.cursor()
	print ' ------ education_exp sync begin ------'
	for row in select_db("""select a.id, b.basic_info_id, a.school, a.major, a.background, a.start_date, a.end_date, a.update_date 
		from personal_educationinfo a, personal_personnelinfo b, staff_basicinfo c, staff_jobinfo d where 
		a.personnel_info_id = b.id and b.Basic_Info_id = c.id and c.id = d.Basic_Info_id and d.end_date is null"""):
		cur_edu.execute("""insert into education_exp(`id`, `staffinfo_id`, `school`, `major`, `degree`, `sdate`, `edate`, `ctime`, `cperson`, `utime`, `uperson`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", 
			[row[0], row[1], unicode_utf8(row[2]), unicode_utf8(row[3]), unicode_utf8(row[4]), unicode_utf8(row[5]), unicode_utf8(row[6]), unicode_utf8(row[7]), 'imported', unicode_utf8(row[7]), 'imported'])
	print ' ------ education_exp sync end ------'
	cur_edu.close()

	# WorkExp
	cur_work = ps_db.cursor()
	print ' ------ work_exp sync begin ------'
	for row in select_db("""select a.id, b.basic_info_id, a.company_name, a.city, a.start_end_date, a.department, a.pay, a.relations, a.lawsuit, a.update_date 
		from personal_workexperienceinfo a, personal_personnelinfo b, staff_basicinfo c, staff_jobinfo d where 
		a.personnel_info_id = b.id and b.Basic_Info_id = c.id and c.id = d.basic_info_id and d.end_date is null"""):
		cur_work.execute("""insert into work_exp(`id`, `staffinfo_id`, `company`, `city`, `sedate`, `department`, `salary`, `is_departure`, `is_lawsuit`, `ctime`, `cperson`, `utime`, `uperson`) 
			VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", [row[0], row[1], unicode_utf8(row[2]), unicode_utf8(row[3]), unicode_utf8(row[4]),
			unicode_utf8(row[5]), unicode_utf8(row[6]), 1 if row[7] == 'Yes' else 0, 1 if row[8] == u'有 Yes' else 0, unicode_utf8(row[9]), 'imported', unicode_utf8(row[9]), 'imported'])
	print ' ------ work_exp sync end ------'
	cur_work.close()

	# MokiExp
	cur_mokiexp = ps_db.cursor()
	print ' ------ moki_exp sync begin ------'
	for row in select_db("""select b.id, b.basic_info_id, b.start_date, b.end_date, b.Job_Nature, b.Job_Level, b.Employment_Type, '' details, b.reason_of_change, b.update_date, c.username  
		from staff_basicinfo a, staff_jobinfo b, auth_user c where a.id = b.basic_info_id and b.end_date is not null and a.Employment_Status = 'W' and b.update_by_id =c.id """):
		cur_mokiexp.execute("""insert into moki_exp(`id`, `staffinfo_id`, `sdate`, `edate`, `job_nature`, `job_level`, `emp_type`, `details`, `reason`, `ctime`, `cperson`, `utime`, `uperson`) VALUES
		(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", [row[0], row[1], row[2], unicode_utf8(row[3]), unicode_utf8(row[4]), unicode_utf8(row[5]), unicode_utf8(row[6]), unicode_utf8(row[7]), 
		unicode_utf8(row[8]), unicode_utf8(row[9]), unicode_utf8(row[10]), unicode_utf8(row[9]), unicode_utf8(row[10])])
	print ' ------ moki_exp sync end ------'
	cur_mokiexp.close()
	# Update 
	cur_product_team = ps_db.cursor()
	print ' ------ staff_info_production_team sync begin ------'
	for row in select_db("""select a.id, b.basic_info_id, a.productionteam_id from staff_jobinfo_Production_Team a, staff_jobinfo b where a.jobinfo_id = b.id and b.end_date is null"""):
		print row[0], row[1], row[2]
		cur_product_team.execute("""insert into staff_info_production_team(`id`, `staffinfo_id`, `productionteam_id`) VALUES(%s, %s, %s) """, [row[0], row[1], row[2]])
	print ' ------ staff_info_production_team sync end ------'
	cur_product_team.close()

	cur_divhead = ps_db.cursor()
	print ' ------ update division head begin ------'
	for row in select_db(""" select b.id, b.division_head_id from organization_businessdivision b"""):
		print row[0], row[1]
		cur_divhead.execute("""update division a set a.head = %s where a.id =%s""", [row[1], row[0]])
	cur_divhead.close()
	print ' ------ update division head end ------'

	ps_db.commit()
	ps_db.close()
